/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[fnIO_GetSubdirectories]
#-- Purpose:		Returns a list of sub-directories in a directory, and 
#--                 optionally all sub-directories
#--	Last Update:	08/02/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
using System.IO;

public partial class UserDefinedFunctions
{
    // http://nlsimmons.com/SQLScraps/?p=71

    [Microsoft.SqlServer.Server.SqlFunction(
        Name = "fnIO_GetSubdirectories",
        FillRowMethodName = "fnIO_GetSubdirectories_FillRow",
        TableDefinition = "subdirectory_name nvarchar(4000)")]
    public static IEnumerable fnIO_GetSubdirectories(string directoryPath, bool recursive)
    {
        List<object[]> resultItems = new List<object[]>();

        if (System.IO.Directory.Exists(directoryPath))
            foreach (string filePath in System.IO.Directory.GetDirectories(directoryPath, "*", (recursive ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)))
            {
                object[] item = new object[1];
                FileInfo fi = new FileInfo(filePath);

                item.SetValue(filePath, 0);
                resultItems.Add(item);
            }

        return resultItems;
    }
    public static void fnIO_GetSubdirectories_FillRow(Object obj, out SqlString filePath)
    {
        object[] item = (object[])obj;
        filePath = (SqlString)item[0].ToString();
    }
}
